<?php
//search.php - выполняет поисковый запрос по базе данных
//выдает результат в index.php в html
//


//подключаемся к базе
include_once './db_connect.php';
        
$dbh = mysql_connect(HOST, USER, PSWD) or die("Не могу соединиться с MySQL.");
mysql_select_db(DATABASE) or die("Не могу подключиться к базе.");
$res = mysql_query("SET NAMES utf8");

//Получаем переменные
$project = $_POST['project'];
$drawing = $_POST['drawing'];
$revision = $_POST['revision'];
$part = $_POST['part'];
$status = $_POST['status'];
$title = $_POST['title'];
$date_in_beg = $_POST['date_in_beg'];
$date_in_end = $_POST['date_in_end'];
$transmit_in = $_POST['transmit_in'];
$date_out_beg = $_POST['date_out_beg'];
$date_out_end = $_POST['date_out_end'];
$transmit_out = $_POST['transmit_out'];
$checked = $_POST['checked'];
$aproved = $_POST['aproved'];
$comments = $_POST['comments'];
$only_last_rev = $_POST['only_last_rev'];

//параметры для сортировки: имя и направление
$sort_by = $_POST['sort_by'];
$sort_direction = $_POST['sort_direction'];

//если надо получить только последние ревизии, создаем временную таблицу
$sql = 'CREATE TABLE tmp SELECT project, drawing, MAX(revision) AS max_rev, part FROM '.BASE.' GROUP BY project, drawing, part';
mysql_query($sql);
if (mysql_errno () > 0) echo mysql_errno().'  '.mysql_error().'<br/>';

$sql = "SELECT
".BASE.".uin, ".BASE.".project, ".BASE.".drawing, ".BASE.".revision, ".BASE.".part,
".BASE.".drw_status, ".BASE.".title, ".BASE.".date_in, ".BASE.".transmit_in, ".BASE.".date_out,
".BASE.".transmit_out, ".BASE.".checked, ".BASE.".aproved, ".BASE.".comments, ".BASE.".path
FROM ".BASE;

//если только последние ревизии
if ($only_last_rev == 1) {
    $sql .= " INNER JOIN tmp ON (
    ".BASE.".project = tmp.project
    AND ".BASE.".drawing = tmp.drawing
    AND ".BASE.".part = tmp.part
    AND ".BASE.".revision = tmp.max_rev)";
}

$sql .= " WHERE ".BASE.".project LIKE '%$project%'
AND ".BASE.".drawing LIKE '%$drawing%'
AND ".BASE.".revision LIKE '%$revision%'
AND ".BASE.".part LIKE '%$part%'
AND ".BASE.".drw_status LIKE '%$status%'
AND ".BASE.".title LIKE '%$title%'
AND ".BASE.".transmit_in LIKE '%$transmit_in%'";


if ($transmit_out != '') $sql .= " AND ".BASE.".transmit_out = '$transmit_out'";
if ($checked != 'NULL') $sql .= " AND ".BASE.".checked = '$checked'";
if ($aproved != 'NULL') $sql .= " AND ".BASE.".aproved = '$aproved'";
if ($comments != '') $sql .= " AND ".BASE.".comments LIKE '%$comments%'";
//сравнение дат
if ($date_in_beg != '') $sql .= " AND ".BASE.".date_in >= STR_TO_DATE('$date_in_beg', '%Y-%m-%d')";
if ($date_in_end != '') $sql .= " AND ".BASE.".date_in <= STR_TO_DATE('$date_in_end', '%Y-%m-%d')";
if ($date_out_beg != '') $sql .= " AND ".BASE.".date_out >= STR_TO_DATE('$date_out_beg', '%Y-%m-%d')";
if ($date_out_end != '') $sql .= " AND ".BASE.".date_out <= STR_TO_DATE('$date_out_end', '%Y-%m-%d')";


$sql .= " ORDER BY $sort_by $sort_direction";
//echo $sql.'<br/>';

mysql_query($sql);
if (mysql_errno () > 0) echo mysql_errno().'  '.mysql_error().'<br/>';
$result = mysql_query($sql);

echo '<table align="center" cellspacing="10">';
//$row_number = 0;
if (mysql_num_rows($result) > 0)
 while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    //Определяем цвет строки
    if ($row['checked'] == 1) {
        if ($row['aproved'] == 0) $class = 'fail'; else $class = 'aprove';
    } else $class='';
        
        
    echo "<tr>";
    echo '<td class = "td_button actual" name="'.$row['uin'].'">
                <img src="#" width="24px" height="24px"/>
          </td>';
    echo '<td class = "td_project">'.$row['project'].'</td>';
    echo '<td class = "td_drawing"><a href="'.STORAGE_PATH.SEPARATOR.$row['path'].SEPARATOR.$row['project'].' '.$row['drawing'].'_Rev'.
            sprintf("%02d",$row['revision']).'_'.sprintf("%02d", $row['part']).'.pdf">'.$row['drawing'].'</a></td>';
    echo '<td class = "td_revision">'.$row['revision'].'</td>';
    echo '<td class = "td_part">'.$row['part'].'</td>';
    echo '<td class = "td_status">'.$row['drw_status'].'</td>';
    echo '<td class = "td_title">'.$row['title'].'</td>';
    echo '<td class = "td_date">'.$row['date_in'].'</td>';
    echo '<td class = "td_transmit">'.$row['transmit_in'].'</td>';
    echo '<td class = "td_date">'.$row['date_out'].'</td>';
    echo '<td class = "td_transmit">'.$row['transmit_out'].'</td>';
    
    if ($row['checked'] == 1) $sel_check = 'checked'; else $sel_check = ''; 
    echo '<td class = "td_checked"><input type="checkbox" class="property" name="'.
            $row['uin'].'" value="'.$row['checked'].'" '.$sel_check.'></td>';
    echo '<td class = "td_aproved '.$class.'"/>';
    
    echo '<td class = "td_comments">';

//    echo  $row['comments'].'<br/>';
//    $row['comments'] = str_replace("\r\n", '<br/>', $row['comments']);
    if ($row['comments'] != '') echo '<textarea rows="4">'.$row['comments'].'</textarea>';
    echo '</td>';
    
    echo '</tr>';
  }

else echo 'No results';
echo "</table>";

//удаляем временную таблицу
$sql = "DROP TABLE tmp";
mysql_query($sql);
if (mysql_errno () > 0) echo mysql_errno().'  '.mysql_error().'<br/>';

//закрываем соединение с базой
mysql_close($dbh);
?>